package com.bw.mapper;

import com.bw.bean.Vo;
import com.bw.bean.Vo1;
import com.bw.bean.Vo2;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Select;

import java.util.List;

@Mapper
public interface VoMapper {

    @Select("select hour(create_time) 'name',count(*) 'value'\n" +
            "from user_chapter_process\n" +
            "group by hour(create_time) order by hour(create_time)")
    List<Vo> getList1();


    @Select("select ci.course_name 'name',\n" +
            "       count(distinct t.user_id) 'value1',\n" +
            "       avg(t.score) 'value2',\n" +
            "       avg(t.duration_sec) 'value3'\n" +
            "from test_exam t\n" +
            "left join test_paper tp on tp.id = t.paper_id\n" +
            "left join course_info ci on ci.id = tp.course_id\n" +
            "group by ci.course_name")
    List<Vo1> getList2();


    @Select("select count(*) from payment_info")
    Integer getList3();

    @Select("select c.course_name 'name',\n" +
            "       avg(ri.review_stars) 'avg1',\n" +
            "       count(distinct ri.user_id) 'num1',\n" +
            "       sum(if(ri.review_stars=5,1,0)) 'num2',\n" +
            "       sum(if(ri.review_stars=5,1,0))/count(ri.user_id) 'avg2'\n" +
            "    from course_info c\n" +
            "left join review_info ri on c.id = ri.course_id\n" +
            "group by c.course_name")
    List<Vo2> getList4();
}
